/*
 * 增删改查 数据库操作类
 * 
 */
package simple.framework.util;

import java.util.Iterator;
import org.json.*;
import simple.framework.util.dbc.*;

/**
 *
 * @author cxc
 */
public class sql {

    public conf conf;
    private String cmd;

    public sql(conf conf) {
        this.conf = conf;
    }

    /**
     *
     * @param argString 传人进来的JSON变量字符 {"argmt":{"type":"insert",
     * "sqltpl":"insert tab()values()", "datas":[{"a":"d"},{"a":"b"},{"a":"c"}],
     * "retpl":{} } }
     * @return
     */
    public String exec(String argString) {
        String tmpstr = "";
        String tString = argString;
        try {
            JSONObject tJsonObject = json.stringToJSONObject(tString);
            JSONObject tJsonObject1 = (JSONObject) json.getSafeJSONObjectValue(tJsonObject, "argmt");
            if (!cls.isEmpty(tJsonObject1)) {
                String tType = cls.toString(json.getSafeJSONObjectValue(tJsonObject1, "type"));
                if (tType.equals("insert")) {
                    tmpstr = doExecs(tString);
                } else if (tType.equals("update")) {
                    tmpstr = doExecs(tString);
                } else if (tType.equals("del")) {
                    tmpstr = doExecs(tString);
                } else if (tType.equals("select")) {
                    tmpstr = doSelect(tString);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return tmpstr;
    }

    private String doExecs(String argString) {
        String tmpstr = "";
        String tString = argString;
        JSONObject tJsonObject = json.stringToJSONObject(tString);
        JSONObject tJsonArgmt = (JSONObject) json.getSafeJSONObjectValue(tJsonObject, "argmt");
        if (!cls.isEmpty(tJsonArgmt)) {
            String tSql = conf.sp.creplace(cls.toString(json.getSafeJSONObjectValue(tJsonArgmt, "sqltpl")));
            tmpstr = conf.sp.creplace(cls.toString(json.getSafeJSONObjectValue(tJsonArgmt, "retpl")));
            JSONObject tJsonDatas = (JSONObject) json.getSafeJSONObjectValue(tJsonArgmt, "datas");
            String tMessage = "";
            if (!cls.isEmpty(tJsonDatas)) {
                Object tJsonRow = json.getSafeJSONObjectValue(tJsonDatas, "rows");
                JSONArray localJSONArray = new JSONArray();
                try {
                    if (!cls.isEmpty(tJsonRow)) {
                        JSONArray tRows = (JSONArray) tJsonRow;
                        for (int ti = 0; ti < tRows.length(); ti++) {
                            String tsqlStr =tSql;
                            JSONObject localJSONObject1 = json.stringToJSONObject(tRows.get(ti).toString());
                            JSONObject localJSONObject = new JSONObject();
                            for (Iterator tIter = localJSONObject1.keys(); tIter.hasNext();) {
                                String tKey = (String) tIter.next();
                                String tValue = cls.toString(json.getSafeJSONObjectValue(localJSONObject1, tKey));
                                tsqlStr = tsqlStr.replace("{$" + tKey + "}", encode.htmlencode(tValue));
                            }
                            dbc tDbc = db.newInstance(conf);
                            int tCount = tDbc.Executes(tsqlStr);
                            if (cls.toString(conf.isDebug).equals("1")) {
                                localJSONObject.put("_sql", tsqlStr);
                                localJSONObject.put("_status", tDbc.getRState());
                                localJSONObject.put("_emsg", tDbc.getEMessage());
                            }
                            localJSONObject.put("_data", tRows.get(ti).toString());
                            localJSONObject.put("_count", tCount);
                            localJSONArray.put(localJSONObject);
                        }
                        tmpstr = tmpstr.replace("{$-total}", cls.toString(tRows.length()));
                    }
                } catch (JSONException ex) {
                }
                tMessage = localJSONArray.toString();
            }
            tmpstr = tmpstr.replace("{$-message}", tMessage);
        }
        return tmpstr;
    }

    public String doSelect(String argString) {
        String tmpstr = "";
        String tString = argString;
        JSONObject tJsonObject = json.stringToJSONObject(tString);
        JSONObject tJsonArgmt = (JSONObject) json.getSafeJSONObjectValue(tJsonObject, "argmt");
        if (!cls.isEmpty(tJsonArgmt)) {
            String tSql = conf.sp.creplace(cls.toString(json.getSafeJSONObjectValue(tJsonArgmt, "sqltpl")));
            tmpstr = conf.sp.creplace(cls.toString(json.getSafeJSONObjectValue(tJsonArgmt, "retpl")));
            JSONObject tJsonDatas = (JSONObject) json.getSafeJSONObjectValue(tJsonArgmt, "datas");
            if (!cls.isEmpty(tJsonDatas)) {
                Object tJsonPage = json.getSafeJSONObjectValue(tJsonDatas, "page");
                Object tJsonCol = json.getSafeJSONObjectValue(tJsonDatas, "columns");
                String tWhereString = cls.toString(json.getSafeJSONObjectValue(tJsonDatas, "where"));
                tSql = tSql.replace("{$-sqlwhere}", tWhereString);
                Object[] tArys = null;
                //判断是否分页           
                if (!cls.isEmpty(tJsonPage)) {
                    String pagesize = cls.toString(json.getSafeJSONObjectValue((JSONObject) tJsonPage, "pagesize"));
                    String pageno = cls.toString(json.getSafeJSONObjectValue((JSONObject) tJsonPage, "pageno"));
                    pagi pagi;
                    pagi = new pagi(conf);
                    pagi.sqlstr = tSql;
                    if (!cls.isEmpty(pageno)) {
                        pagi.pagenum = cls.getNum64(pageno);
                    }
                    if (!cls.isEmpty(pagesize)) {
                        pagi.pagesize = cls.getNum64(pagesize);
                    }
                    pagi.Init();
                    tArys = pagi.getDataAry();
                    tmpstr = tmpstr.replace("{$-total}", cls.toString(pagi.rscount));
                } else {
                    dbc tDbc = db.newInstance(conf);
                    tArys = tDbc.getDataAry(tSql);
                    tmpstr = tmpstr.replace("{$-total}", cls.toString(tArys.length));
                }
                if (!cls.isEmpty(tJsonCol)) {
                    tmpstr = tmpstr.replace("{$-rows}", ary2json(tArys, (JSONArray) tJsonCol));
                }
                
                if (cls.toString(conf.isDebug).equals("1")) {
                    try {
                        JSONObject localJSONObject = new JSONObject(tmpstr);
                        localJSONObject.put("_sql", tSql);
                        tmpstr= localJSONObject.toString();
                    } catch (JSONException ex) {
                    }
                }
            }
        }
        return tmpstr;
    }

    private String ary2json(Object[] Arys, JSONArray JsonCol) {
        String tmpstr = "";
        Object[] tArys = Arys;
        JSONArray tJsonCol = JsonCol;
        JSONArray localJSONArray = new JSONArray();
        try {
            if (tArys != null) {
                for (int tis = 0; tis < tArys.length; tis++) {
                    JSONObject localJSONObject2 = new JSONObject();
                    Object[][] tAry = (Object[][]) tArys[tis];
                    if (tJsonCol.length() > 0) {
                        for (int tc = 0; tc < tJsonCol.length(); tc++) {
                            JSONObject localJSONObject3 = json.stringToJSONObject(tJsonCol.get(tc).toString());
                            String tThString = cls.toString(json.getSafeJSONObjectValue(localJSONObject3, "thname"));
                            String tIdxString = cls.toString(json.getSafeJSONObjectValue(localJSONObject3, "idxname"));
                            String tThFmt = cls.toString(json.getSafeJSONObjectValue(localJSONObject3, "fmt"));
                            if (cls.isEmpty(tIdxString)) {
                                tIdxString = tThString;
                            }
                            Object tColValue = cls.getAryValue(tAry, tIdxString);
                            if (!cls.isEmpty(tThFmt)) {
                                tThFmt = tThFmt.replace("{$-value}", cls.toString(tColValue));
                                tColValue = conf.sp.creplace(tThFmt);
                            }
                            localJSONObject2.put(tThString, tColValue);
                        }
                    } else {

                        for (int ti = 0; ti < tAry.length; ti++) {
                            localJSONObject2.put(cls.toString(tAry[ti][0]), tAry[ti][1]);
                        }
                    }
                    localJSONArray.put(localJSONObject2);
                }
            }
        } catch (JSONException ex) {
        }
        tmpstr = localJSONArray.toString();
        return tmpstr;
    }
    /*
     * public static void main(String[] args) { try { // String tString1 =
     * "{\"argmt\":{\"type\":\"insert\",\"sqltpl\":\"insert
     * tab()values()\",\"datas\":[{\"a\":\"d\"},{\"a\":\"b\"},{\"a\":\"c\"}],\"retpl\":\"\"}}";
     * // conf conf = new conf(); // sql sql = new sql(conf); //
     * sql.exec(tString1); JSONArray ary = new JSONArray(); ary.put(1,
     * "ar1").put(2, "ar2"); JSONObject a = new JSONObject("{ a :\"a1\"}");
     * JSONObject b = new JSONObject("{ b:\"b1\"}");
     *
     * } catch (JSONException ex) { } }
     *
     */
}
